<!DOCTYPE html><html><head><title>MySQL学习笔记（Day015-Day016：索引/B+树/Explain）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'><style>

.note-content  {font-family: 'Helvetica Neue', Arial, 'Hiragino Sans GB', STHeiti, 'Microsoft YaHei', 'WenQuanYi Micro Hei', SimSun, Song, sans-serif;}
</style></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day015-day016索引b树explain">MySQL学习笔记（Day015-Day016：索引/B+树/Explain）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day015-day016索引b树explain">MySQL学习笔记（Day015-Day016：索引/B+树/Explain）</a><ul>
<li><a href="#一-索引">一. 索引</a><ul>
<li><a href="#1-索引的定义">1. 索引的定义</a></li>
<li><a href="#2-二分查找">2. 二分查找</a></li>
</ul>
</li>
<li><a href="#二-二叉树binary-tree">二. 二叉树(Binary Tree)</a><ul>
<li><a href="#1-二叉树的定义">1. 二叉树的定义</a></li>
<li><a href="#2-平衡二叉树avl-树">2. 平衡二叉树（AVL-树）</a></li>
</ul>
</li>
<li><a href="#三-b树b树">三. B树/B+树</a><ul>
<li><a href="#1-b树的定义">1. B树的定义</a></li>
<li><a href="#2-b树的定义">2. B+树的定义</a></li>
<li><a href="#3-b树的作用">3. B+树的作用</a></li>
<li><a href="#3-b树的操作">3. B+树的操作</a></li>
<li><a href="#3-b树的扇出fan-out">3. B+树的扇出(fan out)</a></li>
<li><a href="#4-b树存储数据举例">4. B+树存储数据举例</a></li>
</ul>
</li>
<li><a href="#四-mysql索引">四. MySQL索引</a><ul>
<li><a href="#1-mysql-创建索引">1. MySQL 创建索引</a></li>
<li><a href="#2-mysql-查看索引">2. MySQL 查看索引</a></li>
<li><a href="#3-cardinality基数">3. Cardinality（基数）</a></li>
<li><a href="#4-复合索引">4. 复合索引</a></li>
</ul>
</li>
<li><a href="#五-informationschema一">五. information_schema（一）</a></li>
<li><a href="#六-explain-一">六. EXPLAIN （一）</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-索引">一. 索引</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-索引的定义">1. 索引的定义</h3>

<blockquote>
  <p>索引是对记录按照一个或者多个字段进行排序的一种方式。对表中的某个字段建立索引会创建另一种数据结构，其中保存着字段的值，每个值又指向与它相关的记录。这种索引的数据结构是经过排序的，因而可以对其执行二分查找。且性能较高</p>
</blockquote>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-二分查找">2. 二分查找</h3>

<blockquote>
  <p><strong>摘自《MySQL技术内幕：InnoDB存储引擎（第2版）》5.2.1 小节</strong> <br>
  <code>二分查找法（binary search）</code>也称为折半查找法，用来查找一组有序的记录数组中的某一记录，其基本思想是：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置作为比较对象，如果要找的元素值小于该中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。<em>（O(logN)的时间复杂度）</em></p>
</blockquote>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment"># 给出一个例子，注意该例子已经是升序排序的，且查找 数字 48</span><br><span class="hljs-comment line-number">2.</span>数据：<span class="hljs-number">5</span>， <span class="hljs-number">10</span>， <span class="hljs-number">19</span>， <span class="hljs-number">21</span>， <span class="hljs-number">31</span>， <span class="hljs-number">37</span>， <span class="hljs-number">42</span>， <span class="hljs-number">48</span>， <span class="hljs-number">50</span>， <span class="hljs-number">52</span><br><span class="hljs-comment line-number">3.</span>下标：<span class="hljs-number">0</span>，  <span class="hljs-number">1</span>，  <span class="hljs-number">2</span>，  <span class="hljs-number">3</span>，  <span class="hljs-number">4</span>，  <span class="hljs-number">5</span>，  <span class="hljs-number">6</span>，  <span class="hljs-number">7</span>，  <span class="hljs-number">8</span>，  <span class="hljs-number">9</span><br></code></pre>

<ul><li>步骤一：设<code>low</code>为下标最小值<code>0</code>，<code>high</code>为下标最大值<code>9</code>;</li>
<li>步骤二：通过<code>low</code>和<code>high</code>得到<code>mid</code>，mid=（low + high） / 2，初始时<code>mid</code>为下标<code>4</code><em>(也可以=5，看具体算法实现)</em>；</li>
<li><strong><code>步骤三</code></strong>：<code>mid=4</code>对应的数据值是31，31 &lt; 48（我们要找的数字）；</li>
<li>步骤四：通过二分查找的思路，将<code>low</code>设置为31对应的下标<code>4</code>，<code>high</code>保持不变为<code>9</code>，此时<code>mid</code>为<code>6</code>；</li>
<li><strong><code>步骤五</code></strong>：<code>mid=6</code>对应的数据值是42，42 &lt; 48（我们要找的数字）；</li>
<li>步骤六：通过二分查找的思路，将<code>low</code>设置为42对应的下标<code>6</code>，<code>high</code>保持不变为<code>9</code>，此时<code>mid</code>为<code>7</code>；</li>
<li><p><strong><code>步骤七</code></strong>：<code>mid=7</code>对应的数据值是48，48 == 48（我们要找的数字），查找结束；</p>

<p><strong>通过3次<code>二分查找</code>就找到了我们所要的数字，而顺序查找需8次。</strong></p></li>
</ul>

<hr>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h2 id="二-二叉树binary-tree">二. 二叉树(Binary Tree)</h2>

<blockquote>
  <p><a href="https://en.wikipedia.org/wiki/Binary_tree" target="_blank">二叉树 wiki</a></p>
</blockquote>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<h3 id="1-二叉树的定义">1. 二叉树的定义</h3>

<ul><li>每个节点至多只有二棵子树；</li>
<li>二叉树的子树有左右之分，次序不能颠倒；</li>
<li>一棵深度为k，且有 <span class="" rel="fc834a699a657c0e9d9450a6bbb87496"><span class="MathJax_SVG" id="MathJax-Element-1-Frame" role="textbox" aria-readonly="true" style="font-size: 100%; display: inline-block;"><span><img type="image/png" width="42" height="15" longdesc="__SVG__undefined" src="" style="margin-top:0;margin-bottom:0;"></span></span></span>个节点，称为满二叉树(Full Tree)；</li>
<li><p>一棵深度为k，且root到k-1层的节点树都达到最大，第k层的所有节点都<code>连续集中</code>在最左边，此时为完全二叉树（Complete Tree）</p>

<p><img longdesc="./400px-FullBT_CompleteBT.jpg" alt="complete/Full Tree" title="" type="image/jpeg" src="" class=""></p></li>
</ul>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<h3 id="2-平衡二叉树avl-树">2. 平衡二叉树（AVL-树）</h3>

<blockquote>
  <p><a href="https://en.wikipedia.org/wiki/AVL_tree" target="_blank">平衡二叉树 wiki</a></p>
</blockquote>

<ul><li>左子树和右子树都是平衡二叉树；</li>
<li><p>左子树和右子树的高度差绝对值不超过1；</p>

<ul>
<li>平衡二叉树 <br>
<img longdesc="./Binary_Tree_Order.jpg" alt="平衡二叉查找树" title="" type="image/jpeg" src="" class=""> <br>
<br></li>
<li>非平衡二叉树 <br>
<img longdesc="./NoBalance_Binary_Tree.jpg" alt="Alt text" title="" type="image/jpeg" src="" class=""></li></ul></li>
<li><p><strong>平衡二叉树的遍历</strong> <br>
以上面平衡二叉树的图例为样本，进行遍历：</p>

<ul>
<li><code>前序</code>：<code>6</code>, 3, 2, 5, 7, 8（ROOT节点在开头, <code>中</code>-左-右 顺序）</li>
<li><code>中序</code>：2, 3, 5, <code>6</code>, 7, 8（中序遍历即为升序，左-<code>中</code>-右 顺序）</li>
<li><code>后序</code>：2, 5, 3, 8, 7, <code>6</code>（ROOT节点在结尾，左-右-<code>中</code> 顺序）</li></ul>

<blockquote>
  <p>1：可以通过<code>前序</code>和<code>中序</code> 或者是 <code>后序</code>和<code>中序</code>来推导出一个棵树 <br>
  2：<code>前序</code>或者<code>后序</code>用来得到ROOT节点，<code>中序</code>可以区分左右子树</p>
</blockquote></li>
<li><p><strong>平衡二叉树的旋转</strong></p>

<p><img longdesc="./Tree_Rotation.jpg" alt="二叉树的旋转" title="" type="image/jpeg" src="" class=""></p>

<blockquote>
  <p>需要通过旋转（左旋，右旋）来维护平衡二叉树的平衡，在添加和删除的时候需要有额外的开销。</p>
</blockquote></li>
</ul>

<hr>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<h2 id="三-b树b树">三. B树/B+树</h2>

<blockquote>
  <p><a href="https://en.wikipedia.org/wiki/B-tree" target="_blank">B树  wiki介绍</a> <br>
  <a href="https://en.wikipedia.org/wiki/B%2B_tree" target="_blank">B+树 wiki介绍</a></p>
  
  <p><strong>注意:B树和B+树开头的<code>B</code>不是Binary，而是<code>Balance</code></strong></p>
</blockquote>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h3 id="1-b树的定义">1. B树的定义</h3>

<p>阶为M <em>(节点上关键字(Keys)的个数)</em> 的B树的定义：</p>

<ul><li>每个节点最多有M个孩子；</li>
<li>除了root节点外，每个非叶子(non-leaf)节点至少含有(M/2)个孩子；</li>
<li>如果root节点不为空，则root节点至少要有两个孩子节点；</li>
<li>一个非叶子(non-leaf)节点如果含有K个孩子，则包含k-1个keys；</li>
<li>所有叶子节点都在同一层；</li>
<li>B树中的非叶子(non-leaf)节点也包含了数据部分；</li>
</ul>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h3 id="2-b树的定义">2. B+树的定义</h3>

<p>在B树的基础上，B+树做了如下改进</p>

<ul><li>数据只存储在叶子节点上，非叶子节点只保存索引信息； <br>
<ul>
<li>非叶子节点（索引节点）存储的只是一个Flag，不保存实际数据记录；</li>
<li>索引节点指示该节点的左子树比这个Flag小，而右子树大于等于这个Flag</li></ul></li>
<li>叶子节点本身按照数据的升序排序进行链接(串联起来)； <br>
<ul>
<li>叶子节点中的数据在<code>物理存储上是无序</code>的，仅仅是在<code>逻辑上有序</code>（通过指针串在一起）；</li></ul></li>
</ul>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<h3 id="3-b树的作用">3. B+树的作用</h3>

<ul><li>在块设备上，通过B+树可以有效的存储数据；</li>
<li>所有记录都存储在叶子节点上，非叶子(non-leaf)存储索引(keys)信息；</li>
<li>B+树含有非常高的扇出（fanout），通常超过100，在查找一个记录时，可以有效的减少IO操作；</li>
</ul>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h3 id="3-b树的操作">3. B+树的操作</h3>

<ul><li><p><strong>B+树的插入</strong> <br>
B+树的插入必须保证插入后叶子节点中的记录依然排序。</p>

<ul>
<li>插入操作步骤<em>（引用自姜老师的书《MySQL技术内幕：InnoDB存储引擎（第2版）》 第5.3.1小节）</em> <br>
<img longdesc="./B+_insert.jpg" alt="B+ Insert" title="" type="image/jpeg" src="" class=""></li></ul>

<blockquote>
  <p>B+树总是会保持平衡。但是为了保持平衡对于新插入的键值可能需要做大量的拆分页（split）操作；部分情况下可以通过B+树的旋转来替代拆分页操作，进而达到平衡效果。</p>
</blockquote></li>
<li><p><strong>B+树的删除</strong> <br>
B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶子节点中的记录依然排序。与插入不同的是，删除根据填充因子的变化来衡量。</p>

<ul>
<li>删除操作步骤<em>（引用自姜老师的书《MySQL技术内幕：InnoDB存储引擎（第2版）》 第5.3.2小节）</em> <br>
<img longdesc="./B+_delete.jpg" alt="B+ Delete" title="" type="image/jpeg" src="" class=""></li></ul></li>
</ul>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<h3 id="3-b树的扇出fan-out">3. B+树的扇出(fan out)</h3>

<ul><li><p><strong>B+树图例</strong> <br>
<img longdesc="./B+Example.jpg" alt="B+树的例子" title="" type="image/jpeg" src="" class=""></p>

<ul>
<li>该 B+ 树高度为 2</li>
<li>每叶子页（LeafPage）4条记录</li>
<li><code>扇出数为5</code></li>
<li>叶子节点(LeafPage)由小到大（有序）串联在一起</li></ul></li>
</ul>

<blockquote>
  <p><code>扇出</code>是每个索引节点(Non-LeafPage)指向每个叶子节点(LeafPage)的指针 <br>
  <code>扇出数</code> = 索引节点(Non-LeafPage)可存储的最大关键字个数 + 1 <br>
  图例中的索引节点(Non-LeafPage)最大可以存放4个关键字，但实际使用了3个；</p>
</blockquote>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<h3 id="4-b树存储数据举例">4. B+树存储数据举例</h3>

<p>假设B+树中页的大小是16K，每行记录是200Byte大小，求出树的高度为1，2，3，4时，分别可以存储多少条记录。</p>

<ul><li><p>查看数据表中每行记录的平均大小</p>

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">status</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%employees%"</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">2.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">3.</span>           <span class="hljs-keyword">Name</span>: employees<br><span class="hljs-comment line-number">4.</span>         <span class="hljs-keyword">Engine</span>: <span class="hljs-keyword">InnoDB</span><br><span class="hljs-comment line-number">5.</span>        <span class="hljs-keyword">Version</span>: <span class="hljs-number">10</span><br><span class="hljs-comment line-number">6.</span>     Row_format: Dynamic<br><span class="hljs-comment line-number">7.</span>           <span class="hljs-keyword">Rows</span>: <span class="hljs-number">298124</span><br><span class="hljs-comment line-number">8.</span> Avg_row_length: <span class="hljs-number">51</span>   <span class="hljs-comment">-- 平均长度为51个字节</span><br><span class="hljs-comment line-number">9.</span>    Data_length: <span class="hljs-number">15245312</span><br><span class="hljs-comment line-number">10.</span>Max_data_length: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">11.</span>   Index_length: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">12.</span>      Data_free: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">13.</span> Auto_increment: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">14.</span>    Create_time: <span class="hljs-number">2015</span>-<span class="hljs-number">12</span>-<span class="hljs-number">02</span> <span class="hljs-number">21</span>:<span class="hljs-number">32</span>:<span class="hljs-number">02</span><br><span class="hljs-comment line-number">15.</span>    Update_time: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">16.</span>     Check_time: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">17.</span>      <span class="hljs-keyword">Collation</span>: utf8mb4_general_ci<br><span class="hljs-comment line-number">18.</span>       <span class="hljs-keyword">Checksum</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">19.</span> Create_options: <br><span class="hljs-comment line-number">20.</span>        <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">21.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></li>
<li><p><strong>高度为1</strong></p>

<ul>
<li>16K/200B 约等于 80 个记录（数据结构元信息如指针等忽略不计）</li></ul></li>
<li><p><strong>高度为2</strong> <br>
非叶子节点中存放的仅仅是一个索引信息，包含了<code>Key</code>和<code>Point</code>指针；<code>Point</code>指针在MySQL中固定为<code>6Byte</code>。而<code>Key</code>我们这里假设为<code>8Byte</code>，则单个索引信息即为14个字节，<code>KeySize = 14Byte</code></p>

<ul>
<li>高度为2，即有一个索引节点（索引页），和N个叶子节点</li>
<li>一个索引节点可以存放 16K / KeySize = 16K / 14B = 1142个索引信息，即有（1142 + 1）个扇出，以及有（1142 + 1）个叶子节点（数据页）  <em>（可以简化为1000）</em></li>
<li>数据记录数 = （16K / KeySize + 1）x (16K / 200B) 约等于 80W 个记录</li></ul></li>
<li><p><strong>高度为3</strong> <br>
高度为3的B+树，即ROOT节点有1000个扇出，每个扇出又有1000个扇出指向叶子节点。每个节点是80个记录，所以一共有 8000W个记录</p></li>
<li><p><strong>高度为4</strong> <br>
同高度3一样，高度为4时的记录书为（8000 x 1000）W</p></li>
</ul>

<blockquote>
  <p>上述的8000W等数据只是一个理论值。线上实际使用单个页的记录数字要乘以70%，即第二层需要70% x 70% ，依次类推。</p>
  
  <p>因此在数据库中，B+树的高度一般都在2～4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO，2～4次的IO意味着查询时间只需0.02～0.04秒（假设IOPS=100，当前SSD可以达到50000IOPS）。</p>
</blockquote>

<p>从5.7开始，页的预留大小可以设置了，以减少split操作的概率（空间换时间）</p>

<hr>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<h2 id="四-mysql索引">四. MySQL索引</h2>

</div><div id="wmd-preview-section-18" class="wmd-preview-section preview-content">

<h3 id="1-mysql-创建索引">1. MySQL 创建索引</h3>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/alter-table.html" target="_blank">ALTER TABLE 方式</a> <br>
  <a href="http://dev.mysql.com/doc/refman/5.7/en/create-index.html" target="_blank">CREATE INDEX 方式</a></p>
</blockquote>

</div><div id="wmd-preview-section-19" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- ALTER TABLE</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_index_1(a <span class="hljs-built_in">int</span>, b <span class="hljs-built_in">int</span> , <span class="hljs-keyword">c</span> <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">5.</span>Query OK, 0 rows affected (0.20 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_index_1\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">8.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">9.</span>       <span class="hljs-keyword">Table</span>: test_index_1<br><span class="hljs-comment line-number">10.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_index_1`</span> (<br><span class="hljs-comment line-number">11.</span>  <span class="hljs-string">`a`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">12.</span>  <span class="hljs-string">`b`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">13.</span>  <span class="hljs-string">`c`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">14.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4<br><span class="hljs-comment line-number">15.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_index_1 <span class="hljs-keyword">values</span><br><span class="hljs-comment line-number">18.</span>    -&gt; (<span class="hljs-number">1</span>,<span class="hljs-number">10</span>,<span class="hljs-number">100</span>),(<span class="hljs-number">2</span>,<span class="hljs-number">20</span>,<span class="hljs-number">200</span>),<br><span class="hljs-comment line-number">19.</span>    -&gt; (<span class="hljs-number">3</span>,<span class="hljs-number">30</span>,<span class="hljs-number">300</span>),(<span class="hljs-number">4</span>,<span class="hljs-number">40</span>,<span class="hljs-number">400</span>);</span><br><span class="hljs-comment line-number">20.</span>Query OK, 4 rows affected (0.03 sec)<br><span class="hljs-comment line-number">21.</span>Records: 4  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">22.</span><br><span class="hljs-comment line-number">23.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_1;</span><br><span class="hljs-comment line-number">24.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">25.</span>| a    | b    | c    |<br><span class="hljs-comment line-number">26.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">27.</span>|    1 |   10 |  100 |<br><span class="hljs-comment line-number">28.</span>|    2 |   20 |  200 |<br><span class="hljs-comment line-number">29.</span>|    3 |   30 |  300 |<br><span class="hljs-comment line-number">30.</span>|    4 |   40 |  400 |<br><span class="hljs-comment line-number">31.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">32.</span>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">33.</span><br><span class="hljs-comment line-number">34.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> a=<span class="hljs-number">3</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 看执行计划，使用的是扫描整张表的方式</span><br><span class="hljs-comment line-number">35.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">36.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">37.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">38.</span>        <span class="hljs-keyword">table</span>: test_index_1<br><span class="hljs-comment line-number">39.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">40.</span>         <span class="hljs-keyword">type</span>: ALL<br><span class="hljs-comment line-number">41.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">42.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">43.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">44.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">45.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">4</span><br><span class="hljs-comment line-number">46.</span>     filtered: <span class="hljs-number">25.00</span><br><span class="hljs-comment line-number">47.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">48.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">49.</span><br><span class="hljs-comment line-number">50.</span><span class="hljs-comment">-- 给字段a 增加索引</span><br><span class="hljs-comment line-number">51.</span>mysql&gt; <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> test_index_1 <span class="hljs-keyword">add</span> <span class="hljs-keyword">index</span> idx_a (a);</span>  <span class="hljs-comment">-- 给字段a添加索引。索引名为idx_a</span><br><span class="hljs-comment line-number">52.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">53.</span>Records: 0  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">54.</span><br><span class="hljs-comment line-number">55.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> a=<span class="hljs-number">3</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 看执行计划，使用的key为idx_a，走了索引</span><br><span class="hljs-comment line-number">56.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">57.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">58.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">59.</span>        <span class="hljs-keyword">table</span>: test_index_1<br><span class="hljs-comment line-number">60.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">61.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">ref</span><br><span class="hljs-comment line-number">62.</span>possible_keys: idx_a<br><span class="hljs-comment line-number">63.</span>          <span class="hljs-keyword">key</span>: idx_a<br><span class="hljs-comment line-number">64.</span>      key_len: <span class="hljs-number">5</span><br><span class="hljs-comment line-number">65.</span>          <span class="hljs-keyword">ref</span>: const<br><span class="hljs-comment line-number">66.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">67.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">68.</span>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">69.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">70.</span><br><span class="hljs-comment line-number">71.</span><span class="hljs-comment">-- 使用create index</span><br><span class="hljs-comment line-number">72.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> b=<span class="hljs-number">30</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 同样b字段也没有索引</span><br><span class="hljs-comment line-number">73.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">74.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">75.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">76.</span>        <span class="hljs-keyword">table</span>: test_index_1<br><span class="hljs-comment line-number">77.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">78.</span>         <span class="hljs-keyword">type</span>: ALL<br><span class="hljs-comment line-number">79.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">80.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">81.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">82.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">83.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">4</span><br><span class="hljs-comment line-number">84.</span>     filtered: <span class="hljs-number">25.00</span><br><span class="hljs-comment line-number">85.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">86.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">87.</span><br><span class="hljs-comment line-number">88.</span><span class="hljs-comment">-- 给b字段增加索引</span><br><span class="hljs-comment line-number">89.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">index</span> idx_b <span class="hljs-keyword">on</span> test_index_1 (b);</span><br><span class="hljs-comment line-number">90.</span>Query OK, 0 rows affected (0.14 sec)<br><span class="hljs-comment line-number">91.</span>Records: 0  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">92.</span><br><span class="hljs-comment line-number">93.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> b=<span class="hljs-number">30</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 查看执行计划，使用的key为idx_b，走了索引</span><br><span class="hljs-comment line-number">94.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">95.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">96.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">97.</span>        <span class="hljs-keyword">table</span>: test_index_1<br><span class="hljs-comment line-number">98.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">99.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">ref</span><br><span class="hljs-comment line-number">100.</span>possible_keys: idx_b<br><span class="hljs-comment line-number">101.</span>          <span class="hljs-keyword">key</span>: idx_b<br><span class="hljs-comment line-number">102.</span>      key_len: <span class="hljs-number">5</span><br><span class="hljs-comment line-number">103.</span>          <span class="hljs-keyword">ref</span>: const<br><span class="hljs-comment line-number">104.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">105.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">106.</span>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">107.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-20" class="wmd-preview-section preview-content">

<h3 id="2-mysql-查看索引">2. MySQL 查看索引</h3>

</div><div id="wmd-preview-section-21" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- 方式一</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; desc orders;<br><span class="hljs-comment line-number">5.</span>+<span class="hljs-comment">-----------------+-------------+------+-----+---------+-------+</span><br><span class="hljs-comment line-number">6.</span>| Field           | Type        | Null | Key | Default | Extra |<br><span class="hljs-comment line-number">7.</span>+<span class="hljs-comment">-----------------+-------------+------+-----+---------+-------+</span><br><span class="hljs-comment line-number">8.</span>| o_orderkey      | int(11)     | NO   | PRI | NULL    |       | <span class="hljs-comment">-- 索引</span><br><span class="hljs-comment line-number">9.</span>| o_custkey       | int(11)     | YES  | MUL | NULL    |       | <span class="hljs-comment">-- 索引</span><br><span class="hljs-comment line-number">10.</span>| o_orderstatus   | char(1)     | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">11.</span>| o_totalprice    | double      | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">12.</span>| o_orderDATE     | date        | YES  | MUL | NULL    |       | <span class="hljs-comment">-- 索引</span><br><span class="hljs-comment line-number">13.</span>| o_orderpriority | char(15)    | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">14.</span>| o_clerk         | char(15)    | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">15.</span>| o_shippriority  | int(11)     | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">16.</span>| o_comment       | varchar(79) | YES  |     | NULL    |       |<br><span class="hljs-comment line-number">17.</span>+<span class="hljs-comment">-----------------+-------------+------+-----+---------+-------+</span><br><span class="hljs-comment line-number">18.</span>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">19.</span><br><span class="hljs-comment line-number">20.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">21.</span><span class="hljs-comment">-- 方式二</span><br><span class="hljs-comment line-number">22.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">23.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> orders\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">24.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">25.</span>       <span class="hljs-keyword">Table</span>: orders<br><span class="hljs-comment line-number">26.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`orders`</span> (<br><span class="hljs-comment line-number">27.</span>  <span class="hljs-string">`o_orderkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">28.</span>  <span class="hljs-string">`o_custkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">29.</span>  <span class="hljs-string">`o_orderstatus`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">30.</span>  <span class="hljs-string">`o_totalprice`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">31.</span>  <span class="hljs-string">`o_orderDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">32.</span>  <span class="hljs-string">`o_orderpriority`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">15</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">33.</span>  <span class="hljs-string">`o_clerk`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">15</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">34.</span>  <span class="hljs-string">`o_shippriority`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">35.</span>  <span class="hljs-string">`o_comment`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">79</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">36.</span>  PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`o_orderkey`</span>),  <span class="hljs-comment">-- 索引</span><br><span class="hljs-comment line-number">37.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_o_orderdate`</span> (<span class="hljs-string">`o_orderDATE`</span>), <span class="hljs-comment">-- 索引</span><br><span class="hljs-comment line-number">38.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_o_custkey`</span> (<span class="hljs-string">`o_custkey`</span>),  <span class="hljs-comment">-- 索引</span><br><span class="hljs-comment line-number">39.</span>  <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`orders_ibfk_1`</span> FOREIGN <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`o_custkey`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`customer`</span> (<span class="hljs-string">`c_custkey`</span>)<br><span class="hljs-comment line-number">40.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=latin1<br><span class="hljs-comment line-number">41.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">42.</span><br><span class="hljs-comment line-number">43.</span><br><span class="hljs-comment line-number">44.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">45.</span><span class="hljs-comment">-- 方式三</span><br><span class="hljs-comment line-number">46.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">47.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">index</span> <span class="hljs-keyword">from</span> orders\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">48.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">49.</span>        <span class="hljs-keyword">Table</span>: orders<br><span class="hljs-comment line-number">50.</span>   Non_unique: <span class="hljs-number">0</span>        <span class="hljs-comment">-- 表示唯一的 </span><br><span class="hljs-comment line-number">51.</span>     Key_name: PRIMARY  <span class="hljs-comment">-- key的name是primary</span><br><span class="hljs-comment line-number">52.</span> Seq_in_index: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">53.</span>  Column_name: o_orderkey<br><span class="hljs-comment line-number">54.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">55.</span>  Cardinality: <span class="hljs-number">1306748</span>  <span class="hljs-comment">-- 基数，这个列上不同值的记录数</span><br><span class="hljs-comment line-number">56.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">57.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">58.</span>         <span class="hljs-literal">Null</span>: <br><span class="hljs-comment line-number">59.</span>   Index_type: BTREE    <span class="hljs-comment">-- 索引类型是BTree</span><br><span class="hljs-comment line-number">60.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">61.</span>Index_comment: <br><span class="hljs-comment line-number">62.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">63.</span>        <span class="hljs-keyword">Table</span>: orders<br><span class="hljs-comment line-number">64.</span>   Non_unique: <span class="hljs-number">1</span>       <span class="hljs-comment">-- Non_unique为True，表示不唯一</span><br><span class="hljs-comment line-number">65.</span>     Key_name: i_o_orderdate<br><span class="hljs-comment line-number">66.</span> Seq_in_index: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">67.</span>  Column_name: o_orderDATE<br><span class="hljs-comment line-number">68.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">69.</span>  Cardinality: <span class="hljs-number">2405</span><br><span class="hljs-comment line-number">70.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">71.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">72.</span>         <span class="hljs-literal">Null</span>: YES<br><span class="hljs-comment line-number">73.</span>   Index_type: BTREE<br><span class="hljs-comment line-number">74.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">75.</span>Index_comment: <br><span class="hljs-comment line-number">76.</span>*************************** <span class="hljs-number">3.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">77.</span>        <span class="hljs-keyword">Table</span>: orders<br><span class="hljs-comment line-number">78.</span>   Non_unique: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">79.</span>     Key_name: i_o_custkey<br><span class="hljs-comment line-number">80.</span> Seq_in_index: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">81.</span>  Column_name: o_custkey<br><span class="hljs-comment line-number">82.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">83.</span>  Cardinality: <span class="hljs-number">95217</span><br><span class="hljs-comment line-number">84.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">85.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">86.</span>         <span class="hljs-literal">Null</span>: YES<br><span class="hljs-comment line-number">87.</span>   Index_type: BTREE<br><span class="hljs-comment line-number">88.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">89.</span>Index_comment: <br><span class="hljs-comment line-number">90.</span><span class="hljs-number">3</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">91.</span><br><span class="hljs-comment line-number">92.</span>mysql&gt;  <span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> orders;</span> <br><span class="hljs-comment line-number">93.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">94.</span>| count(*) |<br><span class="hljs-comment line-number">95.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">96.</span>|  1500000 |  <span class="hljs-comment">-- orders中有150W条记录，和Cardinality 是不一致的</span><br><span class="hljs-comment line-number">97.</span>+<span class="hljs-comment">----------+</span><br><span class="hljs-comment line-number">98.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.25</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-2040" class="wmd-preview-section preview-content">

<h3 id="3-cardinality基数">3. Cardinality（基数）</h3>

<p><code>Cardinality</code>表示该索引列上有多少<code>不同的记录</code>，这个是一个<code>预估的值</code>，是采样得到的（由InnoDB触发，采样20个页，进行预估），该值<code>越大越好</code>，即当<code>Cardinality / RowNumber 越接近1越好</code>。表示该列是<code>高选择性的</code>。</p>

<ul><li>高选择性：身份证 、手机号码、姓名、订单号等</li>
<li>低选择性：性别、年龄等</li>
</ul>

<p><strong>即该列是否适合创建索引，就看该字段是否具有高选择性</strong></p></div><div id="wmd-preview-section-23" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt;  <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> lineitem\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">2.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">3.</span>       <span class="hljs-keyword">Table</span>: lineitem<br><span class="hljs-comment line-number">4.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`lineitem`</span> (<br><span class="hljs-comment line-number">5.</span>  <span class="hljs-string">`l_orderkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">6.</span>  <span class="hljs-string">`l_partkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">7.</span>  <span class="hljs-string">`l_suppkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">8.</span>  <span class="hljs-string">`l_linenumber`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">9.</span>  <span class="hljs-string">`l_quantity`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">10.</span>  <span class="hljs-string">`l_extendedprice`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">11.</span>  <span class="hljs-string">`l_discount`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">12.</span>  <span class="hljs-string">`l_tax`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">13.</span>  <span class="hljs-string">`l_returnflag`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">14.</span>  <span class="hljs-string">`l_linestatus`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">15.</span>  <span class="hljs-string">`l_shipDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">16.</span>  <span class="hljs-string">`l_commitDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">17.</span>  <span class="hljs-string">`l_receiptDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">18.</span>  <span class="hljs-string">`l_shipinstruct`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">25</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">19.</span>  <span class="hljs-string">`l_shipmode`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">20.</span>  <span class="hljs-string">`l_comment`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">44</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">21.</span>  PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`l_orderkey`</span>,<span class="hljs-string">`l_linenumber`</span>),  <span class="hljs-comment">-- 两个列作为primary</span><br><span class="hljs-comment line-number">22.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_shipdate`</span> (<span class="hljs-string">`l_shipDATE`</span>),<br><span class="hljs-comment line-number">23.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_suppkey_partkey`</span> (<span class="hljs-string">`l_partkey`</span>,<span class="hljs-string">`l_suppkey`</span>),<br><span class="hljs-comment line-number">24.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_partkey`</span> (<span class="hljs-string">`l_partkey`</span>),<br><span class="hljs-comment line-number">25.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_suppkey`</span> (<span class="hljs-string">`l_suppkey`</span>),<br><span class="hljs-comment line-number">26.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_receiptdate`</span> (<span class="hljs-string">`l_receiptDATE`</span>),<br><span class="hljs-comment line-number">27.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_orderkey`</span> (<span class="hljs-string">`l_orderkey`</span>),<br><span class="hljs-comment line-number">28.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_orderkey_quantity`</span> (<span class="hljs-string">`l_orderkey`</span>,<span class="hljs-string">`l_quantity`</span>),<br><span class="hljs-comment line-number">29.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_commitdate`</span> (<span class="hljs-string">`l_commitDATE`</span>),<br><span class="hljs-comment line-number">30.</span>  <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`lineitem_ibfk_1`</span> FOREIGN <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`l_orderkey`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`orders`</span> (<span class="hljs-string">`o_orderkey`</span>),<br><span class="hljs-comment line-number">31.</span>  <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`lineitem_ibfk_2`</span> FOREIGN <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`l_partkey`</span>, <span class="hljs-string">`l_suppkey`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`partsupp`</span> (<span class="hljs-string">`ps_partkey`</span>, <span class="hljs-string">`ps_suppkey`</span>)<br><span class="hljs-comment line-number">32.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=latin1<br><span class="hljs-comment line-number">33.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">34.</span><br><span class="hljs-comment line-number">35.</span>mysql&gt;  <span class="hljs-keyword">show</span> <span class="hljs-keyword">index</span> <span class="hljs-keyword">from</span> lineitem\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 省略其他输出，只看PRIMARY</span><br><span class="hljs-comment line-number">36.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">37.</span>        <span class="hljs-keyword">Table</span>: lineitem<br><span class="hljs-comment line-number">38.</span>   Non_unique: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">39.</span>     Key_name: PRIMARY<br><span class="hljs-comment line-number">40.</span> Seq_in_index: <span class="hljs-number">1</span>  <span class="hljs-comment">-- 索引中的顺序，该列的顺序为1</span><br><span class="hljs-comment line-number">41.</span>  Column_name: l_orderkey<br><span class="hljs-comment line-number">42.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">43.</span>  Cardinality: <span class="hljs-number">1416486</span>  <span class="hljs-comment">-- 约140W</span><br><span class="hljs-comment line-number">44.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">45.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">46.</span>         <span class="hljs-literal">Null</span>: <br><span class="hljs-comment line-number">47.</span>   Index_type: BTREE<br><span class="hljs-comment line-number">48.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">49.</span>Index_comment: <br><span class="hljs-comment line-number">50.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">51.</span>        <span class="hljs-keyword">Table</span>: lineitem<br><span class="hljs-comment line-number">52.</span>   Non_unique: <span class="hljs-number">0</span><br><span class="hljs-comment line-number">53.</span>     Key_name: PRIMARY<br><span class="hljs-comment line-number">54.</span> Seq_in_index: <span class="hljs-number">2</span>  <span class="hljs-comment">-- 索引中的顺序，该列的顺序为2</span><br><span class="hljs-comment line-number">55.</span>  Column_name: l_linenumber<br><span class="hljs-comment line-number">56.</span>    <span class="hljs-keyword">Collation</span>: A<br><span class="hljs-comment line-number">57.</span>  Cardinality: <span class="hljs-number">5882116</span>  <span class="hljs-comment">-- 约580W</span><br><span class="hljs-comment line-number">58.</span>     Sub_part: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">59.</span>       Packed: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">60.</span>         <span class="hljs-literal">Null</span>: <br><span class="hljs-comment line-number">61.</span>   Index_type: BTREE<br><span class="hljs-comment line-number">62.</span>      <span class="hljs-keyword">Comment</span>: <br><span class="hljs-comment line-number">63.</span>Index_comment:</span><br></code></pre>

<blockquote>
  <p><strong>对应当前例子</strong> <br>
  第一个索引（Seq_in_index = 1）的<code>Cardinality</code>的值表示<code>当前列（l_orderkey）</code>的不重复的值， <br>
  第二个索引（Seq_in_index = 2）的<code>Cardinality</code>的值表示<code>前两列（l_orderkey）和（l_linenumber）</code>不重复的值</p>
</blockquote>

</div><div id="wmd-preview-section-5618" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- SQL-1</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> lineitem <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span>                                                                 <br><span class="hljs-comment line-number">5.</span>+<span class="hljs-comment">------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+----------------------------------------+</span><br><span class="hljs-comment line-number">6.</span>| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipDATE | l_commitDATE | l_receiptDATE | l_shipinstruct    | l_shipmode | l_comment                              |<br><span class="hljs-comment line-number">7.</span>+<span class="hljs-comment">------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+----------------------------------------+</span><br><span class="hljs-comment line-number">8.</span>|          1 |    155190 |      7706 |            1 |         17 |        21168.23 |       0.04 |  0.02 | N            | O            | 1996-03-13 | 1996-02-12   | 1996-03-22    | DELIVER IN PERSON | TRUCK      | blithely regular ideas caj             |<br><span class="hljs-comment line-number">9.</span>|          1 |     67310 |      7311 |            2 |         36 |        45983.16 |       0.09 |  0.06 | N            | O            | 1996-04-12 | 1996-02-28   | 1996-04-20    | TAKE BACK RETURN  | MAIL       | slyly bold pinto beans detect s        |<br><span class="hljs-comment line-number">10.</span>|          1 |     63700 |      3701 |            3 |          8 |         13309.6 |        0.1 |  0.02 | N            | O            | 1996-01-29 | 1996-03-05   | 1996-01-31    | TAKE BACK RETURN  | REG AIR    | deposits wake furiously dogged,        |<br><span class="hljs-comment line-number">11.</span>|          1 |      2132 |      4633 |            4 |         28 |        28955.64 |       0.09 |  0.06 | N            | O            | 1996-04-21 | 1996-03-30   | 1996-05-16    | NONE              | AIR        | even ideas haggle. even, bold reque    |<br><span class="hljs-comment line-number">12.</span>|          1 |     24027 |      1534 |            5 |         24 |        22824.48 |        0.1 |  0.04 | N            | O            | 1996-03-30 | 1996-03-14   | 1996-04-01    | NONE              | FOB        | carefully final gr                     |<br><span class="hljs-comment line-number">13.</span>|          1 |     15635 |       638 |            6 |         32 |        49620.16 |       0.07 |  0.02 | N            | O            | 1996-01-30 | 1996-02-07   | 1996-02-03    | DELIVER IN PERSON | MAIL       | furiously regular accounts haggle bl   |<br><span class="hljs-comment line-number">14.</span>|          2 |    106170 |      1191 |            1 |         38 |        44694.46 |          0 |  0.05 | N            | O            | 1997-01-28 | 1997-01-14   | 1997-02-02    | TAKE BACK RETURN  | RAIL       | carefully ironic platelets against t   |<br><span class="hljs-comment line-number">15.</span>|          3 |      4297 |      1798 |            1 |         45 |        54058.05 |       0.06 |     0 | R            | F            | 1994-02-02 | 1994-01-04   | 1994-02-23    | NONE              | AIR        | blithely s                             |<br><span class="hljs-comment line-number">16.</span>|          3 |     19036 |      6540 |            2 |         49 |        46796.47 |        0.1 |     0 | R            | F            | 1993-11-09 | 1993-12-20   | 1993-11-24    | TAKE BACK RETURN  | RAIL       | final, regular pinto                   |<br><span class="hljs-comment line-number">17.</span>|          3 |    128449 |      3474 |            3 |         27 |        39890.88 |       0.06 |  0.07 | A            | F            | 1994-01-16 | 1993-11-22   | 1994-01-23    | DELIVER IN PERSON | SHIP       | carefully silent pinto beans boost fur |<br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+----------------------------------------+</span><br><span class="hljs-comment line-number">19.</span>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">20.</span><br><span class="hljs-comment line-number">21.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">22.</span><span class="hljs-comment">-- SQL-2</span><br><span class="hljs-comment line-number">23.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">24.</span>mysql&gt; <span class="hljs-keyword">select</span> l_orderkey, l_linenumber <span class="hljs-keyword">from</span> lineitem <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span> <br><span class="hljs-comment line-number">25.</span>+<span class="hljs-comment">------------+--------------+</span><br><span class="hljs-comment line-number">26.</span>| l_orderkey | l_linenumber |<br><span class="hljs-comment line-number">27.</span>+<span class="hljs-comment">------------+--------------+</span><br><span class="hljs-comment line-number">28.</span>|     721220 |            2 |<br><span class="hljs-comment line-number">29.</span>|     842980 |            4 |<br><span class="hljs-comment line-number">30.</span>|     904677 |            1 |<br><span class="hljs-comment line-number">31.</span>|     990147 |            1 |<br><span class="hljs-comment line-number">32.</span>|    1054181 |            1 |<br><span class="hljs-comment line-number">33.</span>|    1111877 |            3 |<br><span class="hljs-comment line-number">34.</span>|    1332613 |            1 |<br><span class="hljs-comment line-number">35.</span>|    1552449 |            2 |<br><span class="hljs-comment line-number">36.</span>|    2167527 |            3 |<br><span class="hljs-comment line-number">37.</span>|    2184032 |            5 |<br><span class="hljs-comment line-number">38.</span>+<span class="hljs-comment">------------+--------------+</span><br><span class="hljs-comment line-number">39.</span>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">40.</span><br><span class="hljs-comment line-number">41.</span><span class="hljs-comment">--- SQL-1和SQL-2其实都是在没有排序的情况下，取出前10条数据。但是结果不一样</span><br><span class="hljs-comment line-number">42.</span><br><span class="hljs-comment line-number">43.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">44.</span><span class="hljs-comment">-- SQL-3</span><br><span class="hljs-comment line-number">45.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">46.</span>mysql&gt; <span class="hljs-keyword">select</span> l_orderkey, l_linenumber <span class="hljs-keyword">from</span> lineitem <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> l_orderkey <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span>  <span class="hljs-comment">-- 和上面的sql相比，多了一个order by的操作</span><br><span class="hljs-comment line-number">47.</span>+<span class="hljs-comment">------------+--------------+</span><br><span class="hljs-comment line-number">48.</span>| l_orderkey | l_linenumber |<br><span class="hljs-comment line-number">49.</span>+<span class="hljs-comment">------------+--------------+</span><br><span class="hljs-comment line-number">50.</span>|          1 |            1 | <span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">51.</span>|          1 |            2 | <span class="hljs-comment">-- 看orderkey为1，对应的linenumber有6条</span><br><span class="hljs-comment line-number">52.</span>|          1 |            3 | <span class="hljs-comment">-- 这就是orderkey的Cardinality仅为140W</span><br><span class="hljs-comment line-number">53.</span>|          1 |            4 | <span class="hljs-comment">-- 而(orderkey + linenumber)就有580W</span><br><span class="hljs-comment line-number">54.</span>|          1 |            5 | <br><span class="hljs-comment line-number">55.</span>|          1 |            6 | <span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">56.</span>|          2 |            1 |<br><span class="hljs-comment line-number">57.</span>|          3 |            1 |<br><span class="hljs-comment line-number">58.</span>|          3 |            2 |<br><span class="hljs-comment line-number">59.</span>|          3 |            3 |<br><span class="hljs-comment line-number">60.</span>+<span class="hljs-comment">------------+--------------+</span><br><span class="hljs-comment line-number">61.</span>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)<br><span class="hljs-comment line-number">62.</span><br><span class="hljs-comment line-number">63.</span><span class="hljs-comment">--- SQL-3 和SQL-2 不同的原因是 他们走了不同的索引</span><br><span class="hljs-comment line-number">64.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> l_orderkey, l_linenumber <span class="hljs-keyword">from</span> lineitem <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">65.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">66.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">67.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">68.</span>        <span class="hljs-keyword">table</span>: lineitem<br><span class="hljs-comment line-number">69.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">70.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">index</span><br><span class="hljs-comment line-number">71.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">72.</span>          <span class="hljs-keyword">key</span>: i_l_shipdate  <span class="hljs-comment">-- 使用了shipdate进行了索引</span><br><span class="hljs-comment line-number">73.</span>      key_len: <span class="hljs-number">4</span><br><span class="hljs-comment line-number">74.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">75.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">5882306</span><br><span class="hljs-comment line-number">76.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">77.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">index</span><br><span class="hljs-comment line-number">78.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">79.</span><br><span class="hljs-comment line-number">80.</span><br><span class="hljs-comment line-number">81.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> l_orderkey, l_linenumber <span class="hljs-keyword">from</span> lineitem <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> l_orderkey <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">82.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">83.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">84.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">85.</span>        <span class="hljs-keyword">table</span>: lineitem<br><span class="hljs-comment line-number">86.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">87.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">index</span><br><span class="hljs-comment line-number">88.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">89.</span>          <span class="hljs-keyword">key</span>: i_l_orderkey  <span class="hljs-comment">-- 使用了orderkey进行了查询</span><br><span class="hljs-comment line-number">90.</span>      key_len: <span class="hljs-number">4</span><br><span class="hljs-comment line-number">91.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">92.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">10</span><br><span class="hljs-comment line-number">93.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">94.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">index</span><br><span class="hljs-comment line-number">95.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">96.</span><br><span class="hljs-comment line-number">97.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> lineitem <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">98.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">99.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">100.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">101.</span>        <span class="hljs-keyword">table</span>: lineitem<br><span class="hljs-comment line-number">102.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">103.</span>         <span class="hljs-keyword">type</span>: ALL   <span class="hljs-comment">-- SQL-1进行了全表扫描</span><br><span class="hljs-comment line-number">104.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">105.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">106.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">107.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">108.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">5882306</span><br><span class="hljs-comment line-number">109.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">110.</span>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">111.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">112.</span><br><span class="hljs-comment line-number">113.</span><span class="hljs-comment">-- 所以，不使用order by取出的结果，可以理解为不是根据主键排序的结果。</span></span><br></code></pre>

<blockquote>
  <p><code>innodb_on_state = off</code> <br>
  在MySQL5.5之前，执行<code>show create table</code>操作会触发采样，而5.5之后将该参数off后，需要主动执行<code>analyze table</code>才会去采样。采样不会锁表或者锁记录。</p>
</blockquote>

</div><div id="wmd-preview-section-6855" class="wmd-preview-section preview-content">

<h3 id="4-复合索引">4. 复合索引</h3>

</div><div id="wmd-preview-section-13278" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt;  <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> lineitem\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">2.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">3.</span>       <span class="hljs-keyword">Table</span>: lineitem<br><span class="hljs-comment line-number">4.</span><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`lineitem`</span> (<br><span class="hljs-comment line-number">5.</span>  <span class="hljs-string">`l_orderkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">6.</span>  <span class="hljs-string">`l_partkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">7.</span>  <span class="hljs-string">`l_suppkey`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">8.</span>  <span class="hljs-string">`l_linenumber`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">9.</span>  <span class="hljs-string">`l_quantity`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">10.</span>  <span class="hljs-string">`l_extendedprice`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">11.</span>  <span class="hljs-string">`l_discount`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">12.</span>  <span class="hljs-string">`l_tax`</span> <span class="hljs-keyword">double</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">13.</span>  <span class="hljs-string">`l_returnflag`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">14.</span>  <span class="hljs-string">`l_linestatus`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">1</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">15.</span>  <span class="hljs-string">`l_shipDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">16.</span>  <span class="hljs-string">`l_commitDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">17.</span>  <span class="hljs-string">`l_receiptDATE`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">18.</span>  <span class="hljs-string">`l_shipinstruct`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">25</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">19.</span>  <span class="hljs-string">`l_shipmode`</span> <span class="hljs-built_in">char</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">20.</span>  <span class="hljs-string">`l_comment`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">44</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-comment line-number">21.</span>  PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`l_orderkey`</span>,<span class="hljs-string">`l_linenumber`</span>),  <span class="hljs-comment">-- 两个列作为primary，这个就是复合索引</span><br><span class="hljs-comment line-number">22.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_shipdate`</span> (<span class="hljs-string">`l_shipDATE`</span>),<br><span class="hljs-comment line-number">23.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_suppkey_partkey`</span> (<span class="hljs-string">`l_partkey`</span>,<span class="hljs-string">`l_suppkey`</span>),<br><span class="hljs-comment line-number">24.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_partkey`</span> (<span class="hljs-string">`l_partkey`</span>),<br><span class="hljs-comment line-number">25.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_suppkey`</span> (<span class="hljs-string">`l_suppkey`</span>),<br><span class="hljs-comment line-number">26.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_receiptdate`</span> (<span class="hljs-string">`l_receiptDATE`</span>),<br><span class="hljs-comment line-number">27.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_orderkey`</span> (<span class="hljs-string">`l_orderkey`</span>),<br><span class="hljs-comment line-number">28.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_orderkey_quantity`</span> (<span class="hljs-string">`l_orderkey`</span>,<span class="hljs-string">`l_quantity`</span>),<br><span class="hljs-comment line-number">29.</span>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`i_l_commitdate`</span> (<span class="hljs-string">`l_commitDATE`</span>),<br><span class="hljs-comment line-number">30.</span>  <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`lineitem_ibfk_1`</span> FOREIGN <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`l_orderkey`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`orders`</span> (<span class="hljs-string">`o_orderkey`</span>),<br><span class="hljs-comment line-number">31.</span>  <span class="hljs-keyword">CONSTRAINT</span> <span class="hljs-string">`lineitem_ibfk_2`</span> FOREIGN <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`l_partkey`</span>, <span class="hljs-string">`l_suppkey`</span>) <span class="hljs-keyword">REFERENCES</span> <span class="hljs-string">`partsupp`</span> (<span class="hljs-string">`ps_partkey`</span>, <span class="hljs-string">`ps_suppkey`</span>)<br><span class="hljs-comment line-number">32.</span>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=latin1<br><span class="hljs-comment line-number">33.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">34.</span><br><span class="hljs-comment line-number">35.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">36.</span><span class="hljs-comment">-- 复合索引举例</span><br><span class="hljs-comment line-number">37.</span><span class="hljs-comment">-- </span><br><span class="hljs-comment line-number">38.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_index_2(a <span class="hljs-built_in">int</span>, b <span class="hljs-built_in">int</span> , <span class="hljs-keyword">c</span> <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">39.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">40.</span><br><span class="hljs-comment line-number">41.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> test_index_2 <span class="hljs-keyword">add</span> <span class="hljs-keyword">index</span>  idx_mul_ab (a, b);</span>        <br><span class="hljs-comment line-number">42.</span>Query OK, 0 rows affected (0.11 sec)<br><span class="hljs-comment line-number">43.</span>Records: 0  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">44.</span><br><span class="hljs-comment line-number">45.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_index_2 <span class="hljs-keyword">values</span><br><span class="hljs-comment line-number">46.</span>    -&gt; (<span class="hljs-number">1</span>,<span class="hljs-number">1</span>,<span class="hljs-number">10</span>),<br><span class="hljs-comment line-number">47.</span>    -&gt; (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">9</span>),<br><span class="hljs-comment line-number">48.</span>    -&gt; (<span class="hljs-number">2</span>,<span class="hljs-number">1</span>,<span class="hljs-number">8</span>),<br><span class="hljs-comment line-number">49.</span>    -&gt; (<span class="hljs-number">2</span>,<span class="hljs-number">4</span>,<span class="hljs-number">15</span>),<br><span class="hljs-comment line-number">50.</span>    -&gt; (<span class="hljs-number">3</span>,<span class="hljs-number">1</span>,<span class="hljs-number">6</span>),<br><span class="hljs-comment line-number">51.</span>    -&gt; (<span class="hljs-number">3</span>,<span class="hljs-number">2</span>,<span class="hljs-number">17</span>);</span><br><span class="hljs-comment line-number">52.</span>Query OK, 6 rows affected (0.04 sec)<br><span class="hljs-comment line-number">53.</span>Records: 6  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">54.</span><br><span class="hljs-comment line-number">55.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> a = <span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">56.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">57.</span>| a    | b    | c    |<br><span class="hljs-comment line-number">58.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">59.</span>|    1 |    1 |   10 |<br><span class="hljs-comment line-number">60.</span>|    1 |    2 |    9 |<br><span class="hljs-comment line-number">61.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">62.</span>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">63.</span><br><span class="hljs-comment line-number">64.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> a = <span class="hljs-number">1</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">65.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">66.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">67.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">68.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">69.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">70.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">ref</span><br><span class="hljs-comment line-number">71.</span>possible_keys: idx_mul_ab<br><span class="hljs-comment line-number">72.</span>          <span class="hljs-keyword">key</span>: idx_mul_ab   <span class="hljs-comment">-- 走了索引</span><br><span class="hljs-comment line-number">73.</span>      key_len: <span class="hljs-number">5</span><br><span class="hljs-comment line-number">74.</span>          <span class="hljs-keyword">ref</span>: const<br><span class="hljs-comment line-number">75.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">2</span><br><span class="hljs-comment line-number">76.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">77.</span>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">78.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">79.</span><br><span class="hljs-comment line-number">80.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> a = <span class="hljs-number">1</span> <span class="hljs-keyword">and</span> b = <span class="hljs-number">2</span>;</span><br><span class="hljs-comment line-number">81.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">82.</span>| a    | b    | c    |<br><span class="hljs-comment line-number">83.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">84.</span>|    1 |    2 |    9 |<br><span class="hljs-comment line-number">85.</span>+<span class="hljs-comment">------+------+------+</span><br><span class="hljs-comment line-number">86.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">87.</span><br><span class="hljs-comment line-number">88.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> a = <span class="hljs-number">1</span> <span class="hljs-keyword">and</span> b = <span class="hljs-number">2</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">89.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">90.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">91.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">92.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">93.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">94.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">ref</span>   <span class="hljs-comment">-- 此时也是走了索引</span><br><span class="hljs-comment line-number">95.</span>possible_keys: idx_mul_ab<br><span class="hljs-comment line-number">96.</span>          <span class="hljs-keyword">key</span>: idx_mul_ab  <br><span class="hljs-comment line-number">97.</span>      key_len: <span class="hljs-number">10</span><br><span class="hljs-comment line-number">98.</span>          <span class="hljs-keyword">ref</span>: const,const<br><span class="hljs-comment line-number">99.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">100.</span>     filtered: <span class="hljs-number">100.00</span><br><span class="hljs-comment line-number">101.</span>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">102.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">103.</span><br><span class="hljs-comment line-number">104.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> b = <span class="hljs-number">2</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 只查询b          </span><br><span class="hljs-comment line-number">105.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">106.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">107.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">108.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">109.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">110.</span>         <span class="hljs-keyword">type</span>: ALL  <span class="hljs-comment">-- 没有使用索引</span><br><span class="hljs-comment line-number">111.</span>possible_keys: <span class="hljs-literal">NULL</span>  <br><span class="hljs-comment line-number">112.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">113.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">114.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">115.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">6</span><br><span class="hljs-comment line-number">116.</span>     filtered: <span class="hljs-number">16.67</span><br><span class="hljs-comment line-number">117.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">118.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">119.</span><br><span class="hljs-comment line-number">120.</span><br><span class="hljs-comment line-number">121.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> a=<span class="hljs-number">1</span> <span class="hljs-keyword">or</span> b = <span class="hljs-number">2</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 使用or，要求结果集是并集</span><br><span class="hljs-comment line-number">122.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">123.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">124.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">125.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">126.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">127.</span>         <span class="hljs-keyword">type</span>: ALL  <span class="hljs-comment">-- 没有使用索引，因为b没有索引，所以b是走全表扫描的，既然走扫描，a的值也可以一起过滤</span><br><span class="hljs-comment line-number">128.</span>                    <span class="hljs-comment">-- 就没有必要在去查一次 a 的索引了</span><br><span class="hljs-comment line-number">129.</span>possible_keys: idx_mul_ab<br><span class="hljs-comment line-number">130.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">131.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">132.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">133.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">6</span><br><span class="hljs-comment line-number">134.</span>     filtered: <span class="hljs-number">30.56</span><br><span class="hljs-comment line-number">135.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">136.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">137.</span><br><span class="hljs-comment line-number">138.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">139.</span><span class="hljs-comment">-- 特别的例子</span><br><span class="hljs-comment line-number">140.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">141.</span><span class="hljs-comment">---- 还是只使用b列去做范围查询，发现是走索引了</span><br><span class="hljs-comment line-number">142.</span><span class="hljs-comment">---- 注意查询的是 count(*)</span><br><span class="hljs-comment line-number">143.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span>  b &gt;<span class="hljs-number">1</span> <span class="hljs-keyword">and</span> b &lt; <span class="hljs-number">3</span>\<span class="hljs-keyword">G</span> <br><span class="hljs-comment line-number">144.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">145.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">146.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">147.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">148.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">149.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">index</span>  <span class="hljs-comment">-- 走了索引 </span><br><span class="hljs-comment line-number">150.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">151.</span>          <span class="hljs-keyword">key</span>: idx_mul_ab<br><span class="hljs-comment line-number">152.</span>      key_len: <span class="hljs-number">10</span><br><span class="hljs-comment line-number">153.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">154.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">6</span><br><span class="hljs-comment line-number">155.</span>     filtered: <span class="hljs-number">16.67</span><br><span class="hljs-comment line-number">156.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span>;</span> Using index  <span class="hljs-comment">-- 覆盖索引</span><br><span class="hljs-comment line-number">157.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">158.</span><br><span class="hljs-comment line-number">159.</span><span class="hljs-comment">-- 因为要求的是count(*), 要求所有的记录的和，</span><br><span class="hljs-comment line-number">160.</span><span class="hljs-comment">-- 那索引a是包含了全部的记录的，即扫描（a,b)的索引也是可以得到count(*)的</span><br><span class="hljs-comment line-number">161.</span><br><span class="hljs-comment line-number">162.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span>  b &gt;<span class="hljs-number">1</span> <span class="hljs-keyword">and</span> b &lt; <span class="hljs-number">3</span>\<span class="hljs-keyword">G</span>        <br><span class="hljs-comment line-number">163.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">164.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">165.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">166.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">167.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">168.</span>         <span class="hljs-keyword">type</span>: ALL  <span class="hljs-comment">-- 查询 * 就没法使用（a，b）索引了，需要全表扫描b的值。</span><br><span class="hljs-comment line-number">169.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">170.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">171.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">172.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">173.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">6</span><br><span class="hljs-comment line-number">174.</span>     filtered: <span class="hljs-number">16.67</span><br><span class="hljs-comment line-number">175.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">176.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">177.</span><br><span class="hljs-comment line-number">178.</span><br><span class="hljs-comment line-number">179.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span>  a = <span class="hljs-number">1</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">c</span> = <span class="hljs-number">10</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">180.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">181.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">182.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">183.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">184.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">185.</span>         <span class="hljs-keyword">type</span>: <span class="hljs-keyword">ref</span>  <span class="hljs-comment">-- 也是走索引的，先用走a索引得到结果集，再用c=10去过滤</span><br><span class="hljs-comment line-number">186.</span>possible_keys: idx_mul_ab<br><span class="hljs-comment line-number">187.</span>          <span class="hljs-keyword">key</span>: idx_mul_ab<br><span class="hljs-comment line-number">188.</span>      key_len: <span class="hljs-number">5</span><br><span class="hljs-comment line-number">189.</span>          <span class="hljs-keyword">ref</span>: const<br><span class="hljs-comment line-number">190.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">2</span><br><span class="hljs-comment line-number">191.</span>     filtered: <span class="hljs-number">16.67</span><br><span class="hljs-comment line-number">192.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">193.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">194.</span><br><span class="hljs-comment line-number">195.</span>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span>  b = <span class="hljs-number">2</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">c</span> = <span class="hljs-number">10</span>\<span class="hljs-keyword">G</span>   <br><span class="hljs-comment line-number">196.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">197.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">198.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">199.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">200.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">201.</span>         <span class="hljs-keyword">type</span>: ALL  <span class="hljs-comment">-- 而b和c是不行的，(b，c)不是有序的</span><br><span class="hljs-comment line-number">202.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">203.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">204.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">205.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">206.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">6</span><br><span class="hljs-comment line-number">207.</span>     filtered: <span class="hljs-number">16.67</span><br><span class="hljs-comment line-number">208.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">209.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-13711" class="wmd-preview-section preview-content">

<h2 id="五-informationschema一">五. information_schema（一）</h2>

</div><div id="wmd-preview-section-16289" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> information_schema;</span><br><span class="hljs-comment line-number">2.</span>Reading table information for completion of table and column names<br><span class="hljs-comment line-number">3.</span>You can turn off this feature to get a quicker startup with -A<br><span class="hljs-comment line-number">4.</span><br><span class="hljs-comment line-number">5.</span>Database changed<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">tables</span>;</span><br><span class="hljs-comment line-number">8.</span>+<span class="hljs-comment">---------------------------------------+</span><br><span class="hljs-comment line-number">9.</span>| Tables_in_information_schema          |<br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">---------------------------------------+</span><br><span class="hljs-comment line-number">11.</span>| CHARACTER_SETS                        |<br><span class="hljs-comment line-number">12.</span>| COLLATIONS                            |<br><span class="hljs-comment line-number">13.</span>| COLLATION_CHARACTER_SET_APPLICABILITY |<br><span class="hljs-comment line-number">14.</span>| COLUMNS                               |<br><span class="hljs-comment line-number">15.</span>| COLUMN_PRIVILEGES                     |<br><span class="hljs-comment line-number">16.</span>| ENGINES                               |<br><span class="hljs-comment line-number">17.</span>| EVENTS                                |<br><span class="hljs-comment line-number">18.</span>| FILES                                 |<br><span class="hljs-comment line-number">19.</span>| GLOBAL_STATUS                         |<br><span class="hljs-comment line-number">20.</span>| GLOBAL_VARIABLES                      |<br><span class="hljs-comment line-number">21.</span>| KEY_COLUMN_USAGE                      |<br><span class="hljs-comment line-number">22.</span>| OPTIMIZER_TRACE                       |<br><span class="hljs-comment line-number">23.</span>| PARAMETERS                            |<br><span class="hljs-comment line-number">24.</span>| PARTITIONS                            |<br><span class="hljs-comment line-number">25.</span>| PLUGINS                               |<br><span class="hljs-comment line-number">26.</span>| PROCESSLIST                           |<br><span class="hljs-comment line-number">27.</span>| PROFILING                             |<br><span class="hljs-comment line-number">28.</span>| REFERENTIAL_CONSTRAINTS               |<br><span class="hljs-comment line-number">29.</span>| ROUTINES                              |<br><span class="hljs-comment line-number">30.</span>| SCHEMATA                              |<br><span class="hljs-comment line-number">31.</span>| SCHEMA_PRIVILEGES                     |<br><span class="hljs-comment line-number">32.</span>| SESSION_STATUS                        |<br><span class="hljs-comment line-number">33.</span>| SESSION_VARIABLES                     |<br><span class="hljs-comment line-number">34.</span>| STATISTICS                            |<br><span class="hljs-comment line-number">35.</span>| TABLES                                |<br><span class="hljs-comment line-number">36.</span>| TABLESPACES                           |<br><span class="hljs-comment line-number">37.</span>| TABLE_CONSTRAINTS                     |<br><span class="hljs-comment line-number">38.</span>| TABLE_PRIVILEGES                      |<br><span class="hljs-comment line-number">39.</span>| TRIGGERS                              |<br><span class="hljs-comment line-number">40.</span>| USER_PRIVILEGES                       |<br><span class="hljs-comment line-number">41.</span>| VIEWS                                 |<br><span class="hljs-comment line-number">42.</span>| INNODB_LOCKS                          |<br><span class="hljs-comment line-number">43.</span>| INNODB_TRX                            |<br><span class="hljs-comment line-number">44.</span>| INNODB_SYS_DATAFILES                  |<br><span class="hljs-comment line-number">45.</span>| INNODB_FT_CONFIG                      |<br><span class="hljs-comment line-number">46.</span>| INNODB_SYS_VIRTUAL                    |<br><span class="hljs-comment line-number">47.</span>| INNODB_CMP                            |<br><span class="hljs-comment line-number">48.</span>| INNODB_FT_BEING_DELETED               |<br><span class="hljs-comment line-number">49.</span>| INNODB_CMP_RESET                      |<br><span class="hljs-comment line-number">50.</span>| INNODB_CMP_PER_INDEX                  |<br><span class="hljs-comment line-number">51.</span>| INNODB_CMPMEM_RESET                   |<br><span class="hljs-comment line-number">52.</span>| INNODB_FT_DELETED                     |<br><span class="hljs-comment line-number">53.</span>| INNODB_BUFFER_PAGE_LRU                |<br><span class="hljs-comment line-number">54.</span>| INNODB_LOCK_WAITS                     |<br><span class="hljs-comment line-number">55.</span>| INNODB_TEMP_TABLE_INFO                |<br><span class="hljs-comment line-number">56.</span>| INNODB_SYS_INDEXES                    |<br><span class="hljs-comment line-number">57.</span>| INNODB_SYS_TABLES                     |<br><span class="hljs-comment line-number">58.</span>| INNODB_SYS_FIELDS                     |<br><span class="hljs-comment line-number">59.</span>| INNODB_CMP_PER_INDEX_RESET            |<br><span class="hljs-comment line-number">60.</span>| INNODB_BUFFER_PAGE                    |<br><span class="hljs-comment line-number">61.</span>| INNODB_FT_DEFAULT_STOPWORD            |<br><span class="hljs-comment line-number">62.</span>| INNODB_FT_INDEX_TABLE                 |<br><span class="hljs-comment line-number">63.</span>| INNODB_FT_INDEX_CACHE                 |<br><span class="hljs-comment line-number">64.</span>| INNODB_SYS_TABLESPACES                |<br><span class="hljs-comment line-number">65.</span>| INNODB_METRICS                        |<br><span class="hljs-comment line-number">66.</span>| INNODB_SYS_FOREIGN_COLS               |<br><span class="hljs-comment line-number">67.</span>| INNODB_CMPMEM                         |<br><span class="hljs-comment line-number">68.</span>| INNODB_BUFFER_POOL_STATS              |<br><span class="hljs-comment line-number">69.</span>| INNODB_SYS_COLUMNS                    |<br><span class="hljs-comment line-number">70.</span>| INNODB_SYS_FOREIGN                    |<br><span class="hljs-comment line-number">71.</span>| INNODB_SYS_TABLESTATS                 |<br><span class="hljs-comment line-number">72.</span>+<span class="hljs-comment">---------------------------------------+</span><br><span class="hljs-comment line-number">73.</span>61 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">74.</span><br><span class="hljs-comment line-number">75.</span><span class="hljs-comment">-- information_schema 数据库相当于一个数据字典。保存了表的元信息。</span><br><span class="hljs-comment line-number">76.</span><br><span class="hljs-comment line-number">77.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> key_column_usage <span class="hljs-keyword">limit</span>  <span class="hljs-number">3</span>\<span class="hljs-keyword">G</span>  <span class="hljs-comment">-- 显示了哪个索引使用了哪个列</span><br><span class="hljs-comment line-number">78.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">79.</span>           CONSTRAINT_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">80.</span>            CONSTRAINT_SCHEMA: burn_test<br><span class="hljs-comment line-number">81.</span>              CONSTRAINT_NAME: PRIMARY<br><span class="hljs-comment line-number">82.</span>                TABLE_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">83.</span>                 TABLE_SCHEMA: burn_test<br><span class="hljs-comment line-number">84.</span>                   TABLE_NAME: Orders <span class="hljs-comment">-- 表名</span><br><span class="hljs-comment line-number">85.</span>                  COLUMN_NAME: order_id  <span class="hljs-comment">-- 索引的名称</span><br><span class="hljs-comment line-number">86.</span>             ORDINAL_POSITION: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">87.</span>POSITION_IN_UNIQUE_CONSTRAINT: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">88.</span>      REFERENCED_TABLE_SCHEMA: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">89.</span>        REFERENCED_TABLE_NAME: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">90.</span>       REFERENCED_COLUMN_NAME: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">91.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">92.</span>           CONSTRAINT_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">93.</span>            CONSTRAINT_SCHEMA: burn_test<br><span class="hljs-comment line-number">94.</span>              CONSTRAINT_NAME: product_name<br><span class="hljs-comment line-number">95.</span>                TABLE_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">96.</span>                 TABLE_SCHEMA: burn_test<br><span class="hljs-comment line-number">97.</span>                   TABLE_NAME: Orders_MV<br><span class="hljs-comment line-number">98.</span>                  COLUMN_NAME: product_name<br><span class="hljs-comment line-number">99.</span>             ORDINAL_POSITION: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">100.</span>POSITION_IN_UNIQUE_CONSTRAINT: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">101.</span>      REFERENCED_TABLE_SCHEMA: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">102.</span>        REFERENCED_TABLE_NAME: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">103.</span>       REFERENCED_COLUMN_NAME: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">104.</span>*************************** <span class="hljs-number">3.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">105.</span>           CONSTRAINT_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">106.</span>            CONSTRAINT_SCHEMA: burn_test<br><span class="hljs-comment line-number">107.</span>              CONSTRAINT_NAME: child_ibfk_1<br><span class="hljs-comment line-number">108.</span>                TABLE_CATALOG: <span class="hljs-keyword">def</span><br><span class="hljs-comment line-number">109.</span>                 TABLE_SCHEMA: burn_test<br><span class="hljs-comment line-number">110.</span>                   TABLE_NAME: <span class="hljs-keyword">child</span><br><span class="hljs-comment line-number">111.</span>                  COLUMN_NAME: parent_id<br><span class="hljs-comment line-number">112.</span>             ORDINAL_POSITION: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">113.</span>POSITION_IN_UNIQUE_CONSTRAINT: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">114.</span>      REFERENCED_TABLE_SCHEMA: burn_test<br><span class="hljs-comment line-number">115.</span>        REFERENCED_TABLE_NAME: <span class="hljs-keyword">parent</span><br><span class="hljs-comment line-number">116.</span>       REFERENCED_COLUMN_NAME: <span class="hljs-keyword">id</span><br><span class="hljs-comment line-number">117.</span><span class="hljs-number">3</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.04</span> sec)<br><span class="hljs-comment line-number">118.</span><br><span class="hljs-comment line-number">119.</span><span class="hljs-comment">-- 作业:  </span><br><span class="hljs-comment line-number">120.</span><span class="hljs-comment">-- 1:Cardinality 在那张表里面</span><br><span class="hljs-comment line-number">121.</span><span class="hljs-comment">-- 2:线上的索引是否可以优化</span></span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-20948" class="wmd-preview-section preview-content">

<h2 id="六-explain-一">六. EXPLAIN （一）</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/explain.html" target="_blank">EXPLAIN 官方文档</a></p>
</blockquote>

<ul><li>explain是解释SQL语句的执行计划，即显示该SQL语句怎么执行的 <br>
<ul>
<li>使用<code>explain</code>的时候，也可以使用<code>desc</code></li></ul></li>
<li>5.6 版本支持DML语句进行explain解释</li>
<li><p>5.6 版本开始支持<code>JSON格式</code>的输出</p>

<p><strong>注意：EXPLAIN查看的是执行计划，做SQL解析，不会去真的执行；且到5.7以后子查询也不会去执行。</strong> <br>
<br></p></li>
<li><p><strong>参数extended</strong></p></li>
</ul></div><div id="wmd-preview-section-24039" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">explain</span> <span class="hljs-keyword">extended</span>  <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span>  b &gt;<span class="hljs-number">1</span> <span class="hljs-keyword">and</span> b &lt; <span class="hljs-number">3</span>\<span class="hljs-keyword">G</span><br><span class="hljs-comment line-number">2.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">3.</span>           <span class="hljs-keyword">id</span>: <span class="hljs-number">1</span><br><span class="hljs-comment line-number">4.</span>  select_type: SIMPLE<br><span class="hljs-comment line-number">5.</span>        <span class="hljs-keyword">table</span>: test_index_2<br><span class="hljs-comment line-number">6.</span>   <span class="hljs-keyword">partitions</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">7.</span>         <span class="hljs-keyword">type</span>: ALL<br><span class="hljs-comment line-number">8.</span>possible_keys: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">9.</span>          <span class="hljs-keyword">key</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">10.</span>      key_len: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">11.</span>          <span class="hljs-keyword">ref</span>: <span class="hljs-literal">NULL</span><br><span class="hljs-comment line-number">12.</span>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">6</span><br><span class="hljs-comment line-number">13.</span>     filtered: <span class="hljs-number">16.67</span><br><span class="hljs-comment line-number">14.</span>        Extra: <span class="hljs-keyword">Using</span> <span class="hljs-keyword">where</span><br><span class="hljs-comment line-number">15.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">2</span> <span class="hljs-keyword">warnings</span> (<span class="hljs-number">0.00</span> sec)  有 <span class="hljs-keyword">warnings</span>，这里相当于提供一个信息返回<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">warnings</span>\<span class="hljs-keyword">G</span> <br><span class="hljs-comment line-number">18.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">19.</span>  <span class="hljs-keyword">Level</span>: <span class="hljs-keyword">Warning</span><br><span class="hljs-comment line-number">20.</span>   Code: <span class="hljs-number">1681</span><br><span class="hljs-comment line-number">21.</span>Message: <span class="hljs-string">'EXTENDED'</span> <span class="hljs-keyword">is</span> deprecated <span class="hljs-keyword">and</span> will be removed <span class="hljs-keyword">in</span> a future <span class="hljs-keyword">release</span>. <span class="hljs-comment">-- 即将被弃用</span><br><span class="hljs-comment line-number">22.</span>*************************** <span class="hljs-number">2.</span> <span class="hljs-keyword">row</span> ***************************  <span class="hljs-comment">-- 显示真正的执行语句</span><br><span class="hljs-comment line-number">23.</span>  <span class="hljs-keyword">Level</span>: Note<br><span class="hljs-comment line-number">24.</span>   Code: <span class="hljs-number">1003</span><br><span class="hljs-comment line-number">25.</span>Message: <span class="hljs-comment">/* select#1 */</span> <span class="hljs-keyword">select</span> <span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`test_index_2`</span>.<span class="hljs-string">`a`</span> <span class="hljs-keyword">AS</span> <span class="hljs-string">`a`</span>,<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`test_index_2`</span>.<span class="hljs-string">`b`</span> <span class="hljs-keyword">AS</span> <span class="hljs-string">`b`</span>,<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`test_index_2`</span>.<span class="hljs-string">`c`</span> <span class="hljs-keyword">AS</span> <span class="hljs-string">`c`</span> <span class="hljs-keyword">from</span> <span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`test_index_2`</span> <span class="hljs-keyword">where</span> ((<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`test_index_2`</span>.<span class="hljs-string">`b`</span> &gt; <span class="hljs-number">1</span>) <span class="hljs-keyword">and</span> (<span class="hljs-string">`burn_test`</span>.<span class="hljs-string">`test_index_2`</span>.<span class="hljs-string">`b`</span> &lt; <span class="hljs-number">3</span>))<br><span class="hljs-comment line-number">26.</span><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<ul><li><strong>参数FORMAT</strong> <br>
<ul>
<li>使用<code>FORMART=JSON</code>不仅仅是为了格式化输出效果，还有其他有用的显示信息。</li>
<li>且当5.6版本后，使用<code>MySQL Workbench</code>，可以使用<code>visual Explain</code>方式显示详细的图示信息。</li></ul></li>
</ul></div><div id="wmd-preview-section-21405" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">explain</span> <span class="hljs-keyword">format</span>=<span class="hljs-keyword">json</span>  <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span>  b &gt;<span class="hljs-number">1</span> <span class="hljs-keyword">and</span> b &lt; <span class="hljs-number">3</span>\<span class="hljs-keyword">G</span>          <br><span class="hljs-comment line-number">2.</span>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-comment line-number">3.</span><span class="hljs-keyword">EXPLAIN</span>: {<br><span class="hljs-comment line-number">4.</span>  <span class="hljs-string">"query_block"</span>: {<br><span class="hljs-comment line-number">5.</span>    <span class="hljs-string">"select_id"</span>: <span class="hljs-number">1</span>,<br><span class="hljs-comment line-number">6.</span>    <span class="hljs-string">"cost_info"</span>: {<br><span class="hljs-comment line-number">7.</span>      <span class="hljs-string">"query_cost"</span>: <span class="hljs-string">"2.20"</span>  <span class="hljs-comment">-- 总成本</span><br><span class="hljs-comment line-number">8.</span>    },<br><span class="hljs-comment line-number">9.</span>    <span class="hljs-string">"table"</span>: {<br><span class="hljs-comment line-number">10.</span>      <span class="hljs-string">"table_name"</span>: <span class="hljs-string">"test_index_2"</span>,<br><span class="hljs-comment line-number">11.</span>      <span class="hljs-string">"access_type"</span>: <span class="hljs-string">"ALL"</span>,<br><span class="hljs-comment line-number">12.</span>      <span class="hljs-string">"rows_examined_per_scan"</span>: <span class="hljs-number">6</span>,<br><span class="hljs-comment line-number">13.</span>      <span class="hljs-string">"rows_produced_per_join"</span>: <span class="hljs-number">1</span>,<br><span class="hljs-comment line-number">14.</span>      <span class="hljs-string">"filtered"</span>: <span class="hljs-string">"16.67"</span>,<br><span class="hljs-comment line-number">15.</span>      <span class="hljs-string">"cost_info"</span>: {<br><span class="hljs-comment line-number">16.</span>        <span class="hljs-string">"read_cost"</span>: <span class="hljs-string">"2.00"</span>,<br><span class="hljs-comment line-number">17.</span>        <span class="hljs-string">"eval_cost"</span>: <span class="hljs-string">"0.20"</span>,<br><span class="hljs-comment line-number">18.</span>        <span class="hljs-string">"prefix_cost"</span>: <span class="hljs-string">"2.20"</span>,<br><span class="hljs-comment line-number">19.</span>        <span class="hljs-string">"data_read_per_join"</span>: <span class="hljs-string">"16"</span><br><span class="hljs-comment line-number">20.</span>      },<br><span class="hljs-comment line-number">21.</span>      <span class="hljs-string">"used_columns"</span>: [<br><span class="hljs-comment line-number">22.</span>        <span class="hljs-string">"a"</span>,<br><span class="hljs-comment line-number">23.</span>        <span class="hljs-string">"b"</span>,<br><span class="hljs-comment line-number">24.</span>        <span class="hljs-string">"c"</span><br><span class="hljs-comment line-number">25.</span>      ],<br><span class="hljs-comment line-number">26.</span>      <span class="hljs-string">"attached_condition"</span>: <span class="hljs-string">"((`burn_test`.`test_index_2`.`b` &gt; 1) and (`burn_test`.`test_index_2`.`b` &lt; 3))"</span><br><span class="hljs-comment line-number">27.</span>    }<br><span class="hljs-comment line-number">28.</span>  }<br><span class="hljs-comment line-number">29.</span>}<br><span class="hljs-comment line-number">30.</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> <span class="hljs-keyword">warning</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>